﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;

using System.Text;
using KPIS.DBM;
using KPIS.GERP.ESS.MODEL;

namespace KPIS.GERP.ESS.DAL
{
    public class D_BgmRequestTemp
    {
        ConvertNullable.ConvertNullable cn = new ConvertNullable.ConvertNullable();

        public D_BgmRequestTemp()
        {

        }

        public DataTable GetBgmRequestData(string bgm_req_seq)
        {
            string strSQL;
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@bgm_req_seq", bgm_req_seq);

                strSQL = "select *"
                    + " from bgm_request"
                    + " where bgm_req_seq = @bgm_req_seq";

                return dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public string GetBgmStratListCode(string bgm_strat_list_seq, string bgm_year)
        {
            string strSQL;
            string strResult = "";
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@bgm_strat_list_seq", bgm_strat_list_seq);
                dbManager.AddParameters(1, "@bgm_year", bgm_year);

                strSQL = "select bgm_strat_list_code"
                    + " from bgm_strategy_data_mapping"
                    + " where bgm_strat_list_seq = @bgm_strat_list_seq"
                    + " and bgm_year = @bgm_year";

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                if (dtb.Rows.Count > 0)
                {
                    strResult = dtb.Rows[0]["bgm_strat_list_code"].ToString();
                }

                return strResult;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void ChangeStatus(Nullable<int> bgm_req_seq, string record_status)
        {
            string strSQL;
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@bgm_req_seq", bgm_req_seq);
                dbManager.AddParameters(1, "@record_status", record_status);

                strSQL = "update bgm_request"
                    + " set record_status = @record_status"
                    + " where bgm_req_seq = @bgm_req_seq";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public string GetNextProjectCode(string bgm_year, string bgm_strat_list_seq)
        {
            string strSQL;
            DataTable dtb;
            string strResult1 = "";
            string strResult2 = "";
            int nRunningNo = 0;
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_year", bgm_year);
                dbManager.AddParameters(2, "@bgm_strat_list_seq", bgm_strat_list_seq);

                strSQL = "select max(bgm_proj_code) as max_bgm_proj_code"
                    + ", bgm_year, bgm_strat_list_seq"
                    + " from bgm_project"
                    + " where bgm_year = @bgm_year"
                    + " and bgm_strat_list_seq = @bgm_strat_list_seq"
                    //+ " and record_status <> @delete_status" ********** status = 'D' เป็นการปิดโครงการ **********
                    + " group by bgm_year, bgm_strat_list_seq";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                dbManager.Dispose();

                if (dtb.Rows.Count > 0)
                {
                    strResult1 = dtb.Rows[0]["max_bgm_proj_code"].ToString();

                    if (strResult1.Length > 0)
                    {
                        strResult2 = strResult1.Substring(strResult1.LastIndexOf('-') + 1, SystemInfo._intProjectRunningNoLength);
                        strResult1 = strResult1.Substring(0, strResult1.LastIndexOf('-'));

                        if (strResult2.Length > 0 && int.TryParse(strResult2, out nRunningNo))
                        {
                            nRunningNo++;
                            strResult1 = strResult1+ "-" + nRunningNo.ToString("000000000".Substring(0, SystemInfo._intProjectRunningNoLength));
                        }
                    }
                    else
                    {
                        strResult1 = dtb.Rows[0]["bgm_year"].ToString() + "-" + GetBgmStratListCode(dtb.Rows[0]["bgm_strat_list_seq"].ToString(), dtb.Rows[0]["bgm_year"].ToString())
                            + "-" + "000000000".Substring(0, SystemInfo._intProjectRunningNoLength - 1) + "1";
                    }
                }

                return strResult1.Replace("--", "-");
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void PortBgmRequestToBgmProject(Nullable<int> bgm_req_seq)
        {
            string strSQL;
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            try
            {
                DataTable dtbReq = GetBgmRequestData(bgm_req_seq.ToString());
                string strCode = "";// GetNextProjectCode(dtbReq.Rows[0]["bgm_year"].ToString(), dtbReq.Rows[0]["bgm_strat_list_seq"].ToString());

                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@bgm_req_seq", bgm_req_seq);
                dbManager.AddParameters(1, "@code", strCode);

                strSQL = "insert into bgm_project"
                    + " ("
                    + " bgm_proj_code"
                    + ",bgm_proj_name"
                    + ",bgm_proj_descr"
                    + ",bgm_proj_manager"
                    + ",bgm_proj_scope"
                    + ",bgm_proj_date"
                    + ",bgm_proj_reason"
                    + ",bgm_proj_object"
                    + ",bgm_proj_target"
                    + ",bgm_proj_plan"
                    + ",bgm_proj_aim"
                    + ",bgm_proj_profit"
                    + ",bgm_proj_remark"
                    + ",bgm_strat_list_seq"
                    + ",prj_type_seq"
                    + ",job_site_seq"
                    + ",bgm_year"
                    + ",date_start"
                    + ",date_end"
                    + ")"
                    + "(select" 
                    + " @code"
                    + ",bgm_req_name"
                    + ",bgm_req_descr"
                    + ",bgm_req_manager"
                    + ",bgm_req_scope"
                    + ",bgm_req_date"
                    + ",bgm_req_reason"
                    + ",bgm_req_object"
                    + ",bgm_req_target"
                    + ",bgm_req_plan"
                    + ",bgm_req_aim"
                    + ",bgm_req_profit"
                    + ",bgm_req_remark"
                    + ",bgm_strat_list_seq"
                    + ",prj_type_seq"
                    + ",job_site_seq"
                    + ",bgm_year"
                    + ",date_start"
                    + ",date_end"
                    + " from bgm_request"
                    + " where bgm_req_seq = @bgm_req_seq"
                    + ");"
                    + " select scope_identity()";
                string bgm_proj_seq = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0].ToString();

                strSQL = "select prj_type_seq"
                    + " from bgm_project"
                    + " where bgm_proj_seq = " + bgm_proj_seq;
                bool bOutPlan = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0]["prj_type_seq"].ToString() == "4";// Hard code
                
                strSQL = "insert into bgm_project_expense"
                    + " ("
                    + " bgm_proj_seq"
                    + ",descr"
                    + ",total_amount"
                    + ",total_amount_out_plan"
                    + ",remark"
                    + ",mtef1"
                    + ",mtef2"
                    + ",mtef3"
                    + ",bgm_source_seq"
                    + ",bgm_exp_seq"
                    + ",icm_res_item_seq"
                    + ",unit_price"
                    + ",unit_qty"
                    + ")"
                    + "("
                    + "select "
                    + " " + bgm_proj_seq + " as bgm_proj_seq"
                    + ",descr"
                    + (bOutPlan ? ", 0" : ", total_amount" )
                    + (!bOutPlan ? ", 0" : ", total_amount as total_amount_out_plan")
                    + ",remark"
                    + ",mtef1"
                    + ",mtef2"
                    + ",mtef3"
                    + ",bgm_source_seq"
                    + ",bgm_exp_seq"
                    + ",icm_res_item_seq"
                    + ",unit_price"
                    + ",unit_qty"
                    + " from bgm_request_expense"
                    + " where bgm_req_seq = @bgm_req_seq"
                    + ")";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                strSQL = "insert into bgm_project_manager"
                    + " ("
                    + " bgm_proj_seq"
                    + ", emp_seq"
                    + ")"
                    + "("
                    + "select "
                    + " " + bgm_proj_seq + " as bgm_proj_seq"
                    + ",emp_seq"
                    + " from bgm_request_manager"
                    + " where bgm_req_seq = @bgm_req_seq"
                    + ")";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                strSQL = "update bgm_project"
                    + " set record_status = 'A'"
                    + " where bgm_proj_seq = " + bgm_proj_seq;
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                strSQL = "insert into bgm_project_report"
                    + "("
                    + " bgm_proj_exp_seq"
                    + ",used_amount"
                    + ",cal_amount"
                    + ", record_status"
                    + ", rpt_type"
                    + ")"
                    + "("
                    + "select bgm_proj_exp_seq"
                    + (bOutPlan ? ", 0" : ", total_amount") + " as used_amount"
                    + (bOutPlan ? ", 0" : ", total_amount") + " as cal_amount"
                    + ", 'U'"
                    + ", 1"
                    + " from bgm_project_expense"
                    + " where bgm_proj_seq = " + bgm_proj_seq
                    + ")";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                strSQL = "insert into bgm_project_option"
                    + "(bgm_proj_seq"
                    + ",descr"
                    + ",weight"
                    + ",bgm_option_seq"
                    + ")"
                    + "("
                    + "select " + bgm_proj_seq + " as bgm_proj_seq"
                    + ",descr"
                    + ",weight"
                    + ",bgm_option_seq"

                    + " from bgm_request_option"
                    + " where bgm_req_seq = @bgm_req_seq"
                    + ")";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void PortSystem1ToSystem2(Nullable<int> bgm_req_seq)
        {
            string strSQL;
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                var x = ConfigurationSettings.AppSettings["System2.DBName"];
                dbManager.AddParameters(0, "@bgm_req_seq", bgm_req_seq);
                dbManager.AddParameters(1, "@dbname", x);

                strSQL = "select count(*) as count_row"
                    + " from bgm_request_expense"
                    + " where bgm_req_seq = " + bgm_req_seq
                    + " and bgm_source_seq in (6,7)"
                    + " and coalesce(total_amount, 0) > 0";
                bool bOK = Convert.ToInt32(dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0]["count_row"]) > 0;

                if (bOK)
                {
                    strSQL = "insert into @dbname.bgm_request"
                        + " (bgm_req_seq"
                        + ", bgm_req_code"
                        + ",bgm_req_name"
                        + ",bgm_req_descr"
                        + ",bgm_req_manager"
                        + ",bgm_req_scope"
                        + ",bgm_req_date"
                        + ",bgm_req_reason"
                        + ",bgm_req_object"
                        + ",bgm_req_target"
                        + ",bgm_req_plan"
                        + ",bgm_req_aim"
                        + ",bgm_req_profit"
                        + ",bgm_req_remark"
                        + ",bgm_strat_list_seq"
                        + ",prj_type_seq"
                        + ",job_site_seq"
                        + ",bgm_year"
                        + ",date_start"
                        + ",date_end"
                        + ")"
                        + "(select bgm_req_seq"
                        + ", bgm_req_code"
                        + ",bgm_req_name"
                        + ",bgm_req_descr"
                        + ",bgm_req_manager"
                        + ",bgm_req_scope"
                        + ",bgm_req_date"
                        + ",bgm_req_reason"
                        + ",bgm_req_object"
                        + ",bgm_req_target"
                        + ",bgm_req_plan"
                        + ",bgm_req_aim"
                        + ",bgm_req_profit"
                        + ",bgm_req_remark"
                        + ",bgm_strat_list_seq"
                        + ",prj_type_seq"
                        + ",job_site_seq"
                        + ",bgm_year"
                        + ",date_start"
                        + ",date_end"
                        + " from gerp.bgm_request"
                        + " where bgm_req_seq = @bgm_req_seq"
                        + ")";
                    dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                    strSQL = "select prj_type_seq"
                        + " from @dbname.bgm_request"
                        + " where bgm_req_seq = " + bgm_req_seq;
                    bool bOutPlan = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0]["prj_type_seq"].ToString() == "4";// Hard code

                    strSQL = "insert into @dbname.bgm_request_expense"
                        + " (bgm_req_exp_seq"
                        + ", bgm_req_seq"
                        + ",descr"
                        + ", total_amount"
                        + ",remark"
                        + ",mtef1"
                        + ",mtef2"
                        + ",mtef3"
                        + ",bgm_source_seq"
                        + ",bgm_exp_seq"
                        + ",icm_res_item_seq"
                        + ",unit_price"
                        + ",unit_qty"
                        + ")"
                        + "("
                        + "select bgm_req_exp_seq"
                        + " bgm_req_seq"
                        + ",descr"
                        + ", total_amount"
                        + ",remark"
                        + ",mtef1"
                        + ",mtef2"
                        + ",mtef3"
                        + ",2"
                        + ",bgm_exp_seq"
                        + ",icm_res_item_seq"
                        + ",unit_price"
                        + ",unit_qty"
                        + " from bgm_request_expense"
                        + " where bgm_req_seq = @bgm_req_seq"
                        + " and bgm_source_seq in (6,7)"
                        + ")";
                    dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                }
            }
            catch (Exception ex)
            {
                //throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
